package dao.impl;

import dao.SubjectDao;
import model.Subject;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * The type Subject dao.
 *
 * @author wyt
 * @create 2022 -12-12 13:05
 */
public class SubjectDaoImpl implements SubjectDao {
    /**
     * The Pstmt.
     */
    PreparedStatement pstmt = null;
    /**
     * The Rs.
     */
    ResultSet rs = null;

    @Override
    public Subject get(long sid) {
        Subject subject = null;
        String sql = "SELECT * FROM subject WHERE sid=?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, sid);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                subject = new Subject();
                subject.setSid(rs.getLong("sid"));
                subject.setSname(rs.getString("sname"));
                subject.setPrice(rs.getDouble("price"));
                subject.setUid(rs.getLong("uid"));
                subject.setDesc(rs.getString("desc"));
                subject.setSurl(rs.getString("surl"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }

        System.out.println("subject:11111:"+subject);
        return subject;
    }

    @Override
    public List<Subject> findByPage(int pageIndex, int pageSize) {
        List<Subject> subjectList = new ArrayList<>();
        String sql = "SELECT * FROM subject ORDER BY uid  LIMIT ?,?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, (pageIndex - 1) * pageSize);
            pstmt.setInt(2, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Subject subject = new Subject();
                subject.setSid(rs.getLong("sid"));
                subject.setSname(rs.getString("sname"));
                subject.setPrice(rs.getDouble("price"));
                subject.setUid(rs.getLong("uid"));
                subject.setDesc(rs.getString("desc"));
                subject.setSurl(rs.getString("surl"));
                subjectList.add(subject);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return subjectList;
    }

    @Override
    public List<Subject> findByName(String sname, int pageIndex, int pageSize) {
        List<Subject> subjectList = new ArrayList<>();
        String sql = "SELECT * FROM subject where sname LIKE ? LIMIT ?,?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%" + sname + "%");
            pstmt.setInt(2, (pageIndex - 1) * pageSize);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Subject subject = new Subject();
                subject.setSid(rs.getLong("sid"));
                subject.setSname(rs.getString("sname"));
                subject.setPrice(rs.getDouble("price"));
                subject.setUid(rs.getLong("uid"));
                subject.setDesc(rs.getString("desc"));
                subject.setSurl(rs.getString("surl"));
                subjectList.add(subject);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return subjectList;
    }

    @Override
    public List<Subject> findById(long sid, int pageIndex, int pageSize) {
        List<Subject> subjectList = new ArrayList<>();
        String sql = "SELECT * FROM subject where sid= ? LIMIT ?,?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, sid);
            pstmt.setInt(2, (pageIndex - 1) * pageSize);
            pstmt.setInt(3, pageSize);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Subject subject = new Subject();
                subject.setSid(rs.getLong("sid"));
                subject.setSname(rs.getString("sname"));
                subject.setPrice(rs.getDouble("price"));
                subject.setUid(rs.getLong("uid"));
                subject.setDesc(rs.getString("desc"));
                subject.setSurl(rs.getString("surl"));
                subjectList.add(subject);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return subjectList;
    }

    @Override
    public int count() {
        int count = 0;
        String sql = "SELECT count(*) FROM subject";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery(sql);
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public int countById(long sid) {
        int count = 0;
        String sql = "SELECT count(*) FROM subject where sid= ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1,sid);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public int countByName(String sname) {
        int count = 0;
        String sql = "SELECT count(*) FROM subject where sname Like ?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1,"%"+sname+"%");
            rs = pstmt.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(rs, pstmt, conn);
        }
        return count;
    }

    @Override
    public int insert(Subject subject) {
        int rows = 0;
        String sql = "INSERT INTO subject VALUES(?,?,?,?,?,?)";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, subject.getSid());
            pstmt.setString(2, subject.getSname());
            pstmt.setDouble(3, subject.getPrice());
            pstmt.setLong(4, subject.getUid());
            pstmt.setString(5, subject.getDesc());
            pstmt.setString(6,subject.getSurl());
            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return rows;
    }

    @Override
    public int delete(long sid,long uid) {
        int rows = 0;
        String sql = "DELETE FROM subject WHERE sid=? AND uid=?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setLong(1, sid);
            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return rows;
    }

    // 没有
    @Override
    public int update(Subject subject) {
        int rows = 0;
        String sql = "UPDATE subject SET sname=?,price=?,uid=?,desc=? WHERE sid=?";
        Connection conn = BaseDao.getConnection();
        try {
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, subject.getSname());
            pstmt.setDouble(2, subject.getPrice());
            pstmt.setLong(3, subject.getUid());
            pstmt.setString(4, subject.getDesc());
            pstmt.setLong(5, subject.getSid());
            rows = pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.close(null, pstmt, conn);
        }
        return rows;
    }
}
